﻿// seejee_Backendium.Data.Services/AccService.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using seejee_Backendium.Core.Interfaces;
using seejee_Backendium.Core.Models;

namespace seejee_Backendium.Data.Services
{
    public class AccService : IAccService
    {
        private readonly string _connectionString;
        private readonly ILogger<AccService> _logger;

        public AccService(IConfiguration config, ILogger<AccService> logger)
        {
            _connectionString = config.GetConnectionString("Default");
            _logger = logger;
        }

        #region 原有方法修正（适配主键cid）
        /// <summary>
        /// 获取所有数据源记录
        /// </summary>
        public async Task<IEnumerable<AccModel>> GetAllAsync()
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            try
            {
                // 直接查询所有字段（与表结构一致）
                var dataSources = await connection.QueryAsync<AccModel>(
                    "SELECT cid, cIP, cDBName, cDBUser, cDBPassword, cAccName, State FROM SEBI_DataSources");
                return dataSources;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取所有SEBI_DataSources记录异常");
            }
            finally
            {
                await connection.CloseAsync();
            }
            return Enumerable.Empty<AccModel>(); // 返回空集合而非null，避免控制器空引用
        }

        /// <summary>
        /// 根据用户名+密码查询（登录验证）
        /// </summary>
        public async Task<IEnumerable<AccModel>> GetByCusNameAsync(string name, string password)
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            try
            {
                var result = await connection.QueryAsync<AccModel>(
                    @"SELECT cid, cIP, cDBName, cDBUser, cDBPassword, cAccName, State 
                      FROM SEBI_DataSources 
                      WHERE cUserName = @name AND cPassword = @password",
                    new { name, password } // 参数化查询防SQL注入
                );
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据用户名[{0}]查询SEBI_DataSources异常", name);
                return Enumerable.Empty<AccModel>();
            }
            finally
            {
                await connection.CloseAsync();
            }
        }

        /// <summary>
        /// 根据主键cid查询单条记录
        /// </summary>
        public async Task<AccModel?> GetByIdAsync(int cid)
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            try
            {
                var result = await connection.QueryFirstOrDefaultAsync<AccModel>(
                    @"SELECT cid, cIP, cDBName, cDBUser, cDBPassword, cAccName, State 
                      FROM SEBI_DataSources 
                      WHERE cid = @cid",
                    new { cid }
                );
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "根据cid[{0}]查询SEBI_DataSources异常", cid);
                return null;
            }
            finally
            {
                await connection.CloseAsync();
            }
        }
        #endregion

        #region 新增/修改/删除（精准匹配表字段）
        /// <summary>
        /// 新增数据源记录（返回新增的主键cid）
        /// </summary>
        public async Task<int> AddAsync(AccModel model)
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            try
            {
                // SQL字段与表结构完全一致（排除cid，由数据库自增）
                var sql = @"INSERT INTO SEBI_DataSources 
                           (cIP, cDBName, cDBUser, cDBPassword, cAccName, State) 
                           VALUES (@cIP, @cDBName, @cDBUser, @cDBPassword, @cAccName, @State);
                           SELECT CAST(SCOPE_IDENTITY() AS INT)"; // 获取自增主键

                var newCid = await connection.QueryFirstAsync<int>(sql, model);
                return newCid; // 返回新增记录的cid
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "新增SEBI_DataSources记录异常（用户名：{0}）");
                return -1; // 错误标识
            }
            finally
            {
                await connection.CloseAsync();
            }
        }

        /// <summary>
        /// 修改数据源记录（根据cid匹配）
        /// </summary>
        public async Task<bool> UpdateAsync(AccModel model)
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            try
            {
                // 仅更新表中存在的字段，通过cid定位记录
                var sql = @"UPDATE SEBI_DataSources 
                           SET 
                               cIP = @cIP, 
                               cDBName = @cDBName, 
                               cDBUser = @cDBUser, 
                               cDBPassword = @cDBPassword, 
                               cAccName = @cAccName, 
                               State = @State
                           WHERE cid = @cid";

                var affectedRows = await connection.ExecuteAsync(sql, model);
                return affectedRows > 0; // 受影响行数>0表示修改成功
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "修改SEBI_DataSources记录异常（cid：{0}）");
                return false;
            }
            finally
            {
                await connection.CloseAsync();
            }
        }

        /// <summary>
        /// 根据cid删除数据源记录
        /// </summary>
        public async Task<bool> DeleteAsync(int cid)
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            try
            {
                var affectedRows = await connection.ExecuteAsync(
                    "DELETE FROM SEBI_DataSources WHERE cid = @cid",
                    new { cid }
                );
                return affectedRows > 0; // 受影响行数>0表示删除成功
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "删除SEBI_DataSources记录异常（cid：{0}）", cid);
                return false;
            }
            finally
            {
                await connection.CloseAsync();
            }
        }
        #endregion


        public async Task<bool> UpdateStateAsync(int cid, bool state)
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            try
            {
                // 仅更新表中存在的字段，通过cid定位记录
                var sql = @"UPDATE SEBI_DataSources 
                   SET State = @state        
                   WHERE cid = @cid";

                var affectedRows = await connection.ExecuteAsync(sql, new { cid = cid, state = state });
                return affectedRows > 0; // 受影响行数>0表示修改成功
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "修改SEBI_DataSources记录异常（cid：{0}）");
                return false;
            }
            finally
            {
                await connection.CloseAsync();
            }
        }
    }
}